package T02_explain;

import T01_JDBCUtils.JDBCUtils;
import T02_explain.pojo.Customers;
import org.junit.jupiter.api.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * DQL_语句
 * 需求4:查询id为1记录将其封装为map集合
 * 需求5:查询所有记录,将其封装为List
 * 需求6:查询所有记录将其封装为customers的List集合
 * 需求7:查询总的记录数
 */
public class T03_JDBCTemplateDQLTest01 {
    JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());

    /**
     * 需求4:查询id为1记录将其封装为map集合
     */
    @Test
    public  void  test01(){
        String sql="select * from customers where id=?";
        Map<String, Object> stringObjectMap = template.queryForMap(sql, "20");
        System.out.println(stringObjectMap);
    }

    /**
     * 需求5:查询所有记录,将其封装为List
     */
    @Test
    public  void  test02(){
        String sql="select * from customers";
        List<Map<String, Object>> maps = template.queryForList(sql);
        System.out.println(maps);
    }

    /**
     * 需求6:查询所有记录将其封装为customers的List集合  --自定义的
     */
    @Test
    public  void  test03(){
        String sql="select * from customers";
        List<Customers> customers = template.query(sql, new RowMapper<Customers>() {
            @Override
            public Customers mapRow(ResultSet resultSet, int i) throws SQLException {
                Customers customers1=new Customers();
                int id=resultSet.getInt("id");
                String name=resultSet.getString("name");
                String email = resultSet.getString("email");
                Date birth = resultSet.getDate("birth");
                customers1.setId(id);
                customers1.setName(name);
                customers1.setEmail(email);
                customers1.setBirth(birth);
                return customers1;
            }
        });
        customers.forEach(System.out::println);
    }

    /**
     * 需求6:查询所有记录将其封装为customers的List集合  -- RowMapper<Customers>()的实现类  BeanPropertyRowMapper
     */
    @Test
    public  void  test04(){
        String sql="select * from customers";
        List<Customers> customers = template.query(sql, new BeanPropertyRowMapper<>(Customers.class));
        customers.forEach(System.out::println);
    }

    /**
     * 查询一条对象信息
     */
    @Test
    public  void  test05(){
        String sql="select * from customers where id=?";
        Customers customers = template.queryForObject(sql,new BeanPropertyRowMapper<>(Customers.class),20);
        System.out.println(customers);
    }
    /**
     * 查询对象数量
     */
    @Test
    public  void  test06(){
        String sql="select count(*) from customers";
        Long aLong = template.queryForObject(sql, Long.class);
        System.out.println(aLong);
    }
}
